﻿using System;
using Excel;
using System.Drawing;
using System.Text;
using System.Reflection;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.ComponentModel;
using SPW2HO.CommonItems;
using SPW2HO.DataAccess;

namespace SPW2HO.Classes
{
    public class ExcelWriter
    {

        public static int GetTextWidth(string s)
        {
            //System.Windows.Forms.Label l = new System.Windows.Forms.Label();
            //l.Text = s;
            //return l.PreferredWidth;
            return s.Length + 1;
        }
        /// <summary>
        /// Bien doi tu column thanh gia tri
        /// </summary>
        /// <param name="column">Ten cot la dang string</param>
        /// <returns></returns>
        private static int ExcelColumnToValue(string column)
        {
            int value = 0;
            int pow = 1;
            for (int i = column.Length - 1; i >= 0; i--)
            {
                if (!Char.IsDigit(column[i]))
                {
                    value += pow * (column[i] - 'A' + 1);
                    pow *= 26;
                }
                else
                {
                    break;
                }
            }
            return value;
        }

        /// <summary>
        /// Bien doi so thu tu cua column thanh ten column
        /// </summary>
        /// <param name="value">So thu tu cua column</param>
        /// <returns></returns>
        private static string ExcelValueToColumn(int value)
        {
            if (value == 0)
            {
                return string.Empty;
            }
            StringBuilder sb = new StringBuilder();
            while (value > 0)
            {
                int digit = value % 26;
                if (digit == 0)
                {
                    sb.Insert(0, 'Z');
                    return sb.Insert(0, ExcelValueToColumn(value / 26 - 1)).ToString();
                }
                sb.Insert(0, (char)(digit + 'A' - 1));
                value = (value - digit) / 26;
            }
            return sb.ToString();
        }

        /// <summary>
        /// Nhay toi column Current + IncrementValue
        /// </summary>
        /// <param name="CurrentColumn">Current column</param>
        /// <param name="IncrementValue"></param>
        /// <returns></returns>
        private static string IncrementExcelColumn(string CurrentColumn, int IncrementValue)
        {
            return ExcelValueToColumn(ExcelColumnToValue(CurrentColumn) + IncrementValue);
        }

        private static void FormatCell(Range ExcelRange, string Value, int FontSize, bool FontBold, int ColumnWidth, bool IsMerged, Color CellColor, XlHAlign HAlign, XlVAlign VAlign, int RowHeight, params object[] args)
        {
            if (IsMerged == true) ExcelRange.Merge(Missing.Value);

            ExcelRange.Value2 = Value;
            ExcelRange.Font.Bold = FontBold;
            ExcelRange.Font.Color = ColorTranslator.ToOle(CellColor);
            ExcelRange.Font.Name = "Arial";

            if (args.Length > 0)
            {
                bool isUnderline = Convert.ToBoolean(args[0]);
                ExcelRange.Font.Underline = isUnderline;
            }

            //ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

            ExcelRange.HorizontalAlignment = HAlign;
            ExcelRange.VerticalAlignment = VAlign;

            if (FontSize > 0) ExcelRange.Font.Size = FontSize;
            if (ColumnWidth > 0) ExcelRange.ColumnWidth = ColumnWidth;
            if (RowHeight > 0) ExcelRange.RowHeight = RowHeight;

        }

        private static void FormatCell(Range ExcelRange, string Value, int FontSize, bool FontBold, int ColumnWidth, bool IsMerged, Color CellColor,Color BackgroundColor, XlHAlign HAlign, XlVAlign VAlign, int RowHeight)
        {
            if (IsMerged == true) ExcelRange.Merge(Missing.Value);

            ExcelRange.Value2 = Value;
            ExcelRange.Font.Bold = FontBold;
            ExcelRange.Font.Color = ColorTranslator.ToOle(CellColor);
            ExcelRange.Interior.Color = ColorTranslator.ToOle(BackgroundColor);
            ExcelRange.Font.Name = "Arial";

            //ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

            ExcelRange.HorizontalAlignment = HAlign;
            ExcelRange.VerticalAlignment = VAlign;

            if (FontSize > 0) ExcelRange.Font.Size = FontSize;
            if (ColumnWidth > 0) ExcelRange.ColumnWidth = ColumnWidth;
            if (RowHeight > 0) ExcelRange.RowHeight = RowHeight;

        }

        private static void FormatRange(Range excelRange, bool blnCenter, Color colorBackGround)
        {
            excelRange.Interior.Color = ColorTranslator.ToOle(colorBackGround);
       
            if (blnCenter)
            {
                excelRange.VerticalAlignment = XlVAlign.xlVAlignCenter;
                excelRange.HorizontalAlignment = XlVAlign.xlVAlignCenter;
            }
        }

        private static void FormatRange(Range excelRange, bool blnCenter, Color colorBackGround, bool blnBorder)
        {
            FormatRange(excelRange, blnCenter, colorBackGround);
            if (blnBorder)
            {
                excelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            }
        }

        #region Create Distributor Excel file
        public static void InsertDistHeader(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow)
        {
            try
            {
                Range ExcelRange;
                string Col = TopLeftCol;
                int Row = TopLeftRow;

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), IncrementExcelColumn(Col, 12) + Row.ToString());
                FormatCell(ExcelRange, "DANH SÁCH NHÀ PHÂN PHỐI", 18, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void InsertTableHeader(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow)
        {
            try
            {
                Range ExcelRange;
                string Col = TopLeftCol;
                int Row = TopLeftRow;

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "STT", 12, true, 7, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "ShipToID", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "SoldToID", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Khu vực", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Vùng", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Tên nhà phân phối", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Địa chỉ", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Điện thoại", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Fax", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Email", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Kênh chính", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Kênh phụ", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Loại NPP", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int InsertDistributorDetail(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow, System.Data.DataTable dt, System.Windows.Forms.Label lblStatus, BackgroundWorker bgw, int cntRegion,int percentComplete)
        {
            try
            {
                Range ExcelRange;
                string Col = TopLeftCol;
                int Row = TopLeftRow;
                int k = 1;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    //lblStatus.Text = string.Format("Thực hiện xuất thông tin của nhà phân phối: {0}...", CommonClass.ConvertTCVNToUnicode(dr[4].ToString()));                    
                    //percentComplete += Convert.ToInt16(85.0 / cntRegion / dt.Rows.Count);
                    //if (percentComplete<=100)
                    //    bgw.ReportProgress(percentComplete);
                    //STT
                    ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                    FormatCell(ExcelRange, k.ToString(), 12, false, 0, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                    ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                    k++;

                    Col = IncrementExcelColumn(Col, 1);
                    for (int j = 0; j < 12; j++)
                    {
                        ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                        FormatCell(ExcelRange, CommonClass.ConvertTCVNToUnicode(dr[j].ToString()), 12, false, 0, true, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                        ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                        Col = IncrementExcelColumn(Col, 1);
                        ExcelRange.EntireColumn.AutoFit();
                    }
                    Col = TopLeftCol;
                    Row++;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return percentComplete;
        }
        #endregion

        #region Create Outlet Excel file
        public static void InsertOutletHeader(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow,string distCode, string distName)
        {
            try
            {
                Range ExcelRange;
                string Col = TopLeftCol;
                int Row = TopLeftRow;

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), IncrementExcelColumn(Col, 8) + Row.ToString());
                FormatCell(ExcelRange, string.Format("DANH SÁCH KHÁCH HÀNG CỦA NHÀ PHÂN PHỐI {0} [{1}]",distCode,distName), 18, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void InsertOutletTableHeader(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow)
        {
            try
            {
                Range ExcelRange;
                string Col = TopLeftCol;
                int Row = TopLeftRow;

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "STT", 12, true, 7, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Mã NPP", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Tên NPP", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Mã cửa hàng", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Loại khách hàng", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Tên cửa hàng", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Địa chỉ 1", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Địa chỉ 2", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Điện thoại", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int InsertOutletDetail(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow, System.Data.DataTable dt, BackgroundWorker bgw,int cntDist,int percentComplete)
        {
            try
            {
                Range ExcelRange;
                string Col = TopLeftCol;
                int Row = TopLeftRow;
                int k = 1;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    ////lblStatus.Text = string.Format("Thực hiện xuất thông tin của nhà phân phối: {0}...", CommonClass.ConvertTCVNToUnicode(dr[4].ToString()));                    
                    //percentComplete += Convert.ToInt16(85000.0 / cntDist / dt.Rows.Count);
                    //if (percentComplete <= 100000)
                    //    bgw.ReportProgress(percentComplete);
                    //STT
                    ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                    FormatCell(ExcelRange, k.ToString(), 12, false, 0, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                    ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                    k++;

                    Col = IncrementExcelColumn(Col, 1);
                    for (int j = 0; j < 8; j++)
                    {
                        ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                        FormatCell(ExcelRange, CommonClass.ConvertTCVNToUnicode(dr[j].ToString()), 12, false, 0, true, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                        ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                        Col = IncrementExcelColumn(Col, 1);
                        ExcelRange.EntireColumn.AutoFit();
                    }
                    Col = TopLeftCol;
                    Row++;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return percentComplete;
        }

        #endregion

        #region Create Target Excel file
        public static string InsertHeaderFile(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow)
        {
            try
            {
                Range ExcelRange;

                string Col = TopLeftCol;
                int Row = TopLeftRow;

                //{ ProgramID
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), IncrementExcelColumn(Col, 12) + (Row + 1).ToString());
                FormatCell(ExcelRange, "Thông tin doanh số hằng tuần theo khách hàng", 28, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return "";
        }

        public static string InsertProgramInfo(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow, ProgramInfoBO programInfo)
        {
            try
            {
                Range ExcelRange;

                string Col = TopLeftCol;
                int Row = TopLeftRow;

                //{ ProgramID
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Mã chương trình:", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);

                ExcelRange = ExcelSheet.get_Range(IncrementExcelColumn(TopLeftCol, 2) + Row.ToString(), IncrementExcelColumn(TopLeftCol, 2) + Row.ToString());
                FormatCell(ExcelRange, programInfo.ProgramId, 12, false, 0, false, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                //} 

                //go to new row
                Row++;

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                FormatCell(ExcelRange, "Diễn giải:", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);

                ExcelRange = ExcelSheet.get_Range(IncrementExcelColumn(TopLeftCol, 2) + Row.ToString(), IncrementExcelColumn(TopLeftCol, 2) + Row.ToString());
                FormatCell(ExcelRange, programInfo.Description, 12, false, 0, false, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return "";
        }

        public static string InsertActualTargetHeader(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow)
        {
            try
            {
                Range ExcelRange;
                string Col = TopLeftCol;
                int Row = TopLeftRow;


                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "STT", 12, true, 3, true, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Mã\nkhách hàng", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Tên\nkhách hàng", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Loại\nkhách hàng", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Tuyến đường", 12, true, 30, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Chỉ tiêu\nđánh giá", 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "DS bình\nquân/tuần \n(của 13 tuần\n trước", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return "";
        }

        public static string InsertActualTargetDynamicHeader(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow, System.Data.DataTable dt)
        {
            try
            {
                Range ExcelRange;
                string Col = TopLeftCol;
                int Row = TopLeftRow;
                string value = "";
                foreach (DataRow dr in dt.Rows)
                {
                    ExcelRange = ExcelSheet.get_Range(Col + (Row + 2).ToString(), Col + (Row + 2).ToString());
                    FormatCell(ExcelRange, dr["Target"].ToString() + " %", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                    ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                    ExcelRange = ExcelSheet.get_Range(IncrementExcelColumn(Col, dt.Rows.Count) + (Row + 2).ToString(), IncrementExcelColumn(Col, dt.Rows.Count) + (Row + 2).ToString());
                    FormatCell(ExcelRange, dr["Target"].ToString() + " %", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                    ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                    //go to next column
                    Col = IncrementExcelColumn(Col, 1);
                }

                Col = TopLeftCol;

                value = "Bình quân tuần";
                ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), IncrementExcelColumn(Col, dt.Rows.Count - 1) + (Row + 1).ToString());
                FormatCell(ExcelRange, value, 12, true, GetTextWidth(value), true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, dt.Rows.Count);
                value = "Tổng cộng 13 tuần";
                ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), IncrementExcelColumn(Col, dt.Rows.Count - 1) + (Row + 1).ToString());
                FormatCell(ExcelRange, value, 12, true, GetTextWidth(value), true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = TopLeftCol;
                value = "Chỉ tiêu HMP + Thực Phẩm";
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), IncrementExcelColumn(Col, dt.Rows.Count * 2 - 1) + Row.ToString());
                FormatCell(ExcelRange, value, 12, true, GetTextWidth(value), true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, dt.Rows.Count * 2);
                value = "Chỉ tiêu Mặt hàng bắt buộc";
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), IncrementExcelColumn(Col, 1) + Row.ToString());
                FormatCell(ExcelRange, value, 12, true, GetTextWidth(value), true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                value = "Bình quân tuần";
                ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, value, 12, true, GetTextWidth(value), true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                value = "Tổng cộng 13 tuần";
                ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, value, 12, true, GetTextWidth(value), true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return "";
        }

        public static string InsertActualTargetDetail(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow, System.Data.DataTable dt, BackgroundWorker bgw)
        {
            try
            {
                Range ExcelRange;
                string Col = TopLeftCol;
                int Row = TopLeftRow;
                int k = 1;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    bgw.ReportProgress(15 + Convert.ToInt16(80.0 / dt.Rows.Count * (i + 1)));
                    if (i == 0 || !dt.Rows[i][1].ToString().Equals(dt.Rows[i - 1][1].ToString()))
                    {
                        int NoOfRowPerCust = 0;
                        while (i + NoOfRowPerCust < dt.Rows.Count)
                        {
                            if (dt.Rows[i][1].ToString().Equals(dt.Rows[i + NoOfRowPerCust][1].ToString()))
                            {
                                NoOfRowPerCust++;
                            }
                            else
                            {
                                break;
                            }
                        }

                        //STT
                        ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + NoOfRowPerCust - 1).ToString());
                        FormatCell(ExcelRange, k.ToString(), 12, false, 0, true, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                        ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                        k++;
                        Col = IncrementExcelColumn(Col, 1);
                        for (int j = 1; j < 5; j++)
                        {
                            //MaKH, TenKH, LoaiKH, Tuyen duong
                            ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + NoOfRowPerCust - 1).ToString());
                            FormatCell(ExcelRange, CommonClass.ConvertTCVNToUnicode(dr[j].ToString()), 12, false, 0, true, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                            ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                            Col = IncrementExcelColumn(Col, 1);
                            ExcelRange.EntireColumn.AutoFit();
                        }
                    }
                    else
                    {
                        Col = IncrementExcelColumn(Col, 5);
                    }

                    //Chi tieu danh gia
                    ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                    FormatCell(ExcelRange, CommonClass.ConvertTCVNToUnicode(dr[5].ToString().Trim()), 12, false, 0, false, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                    ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                    Col = IncrementExcelColumn(Col, 1);
                    ExcelRange.EntireColumn.AutoFit();

                    for (int j = 6; j < dt.Columns.Count; j++)
                    {
                        string value = dr[j].ToString();
                        if (!value.Equals(string.Empty))
                        {
                            ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                            FormatCell(ExcelRange, Convert.ToDouble(value).ToString("#,###.00"), 12, false, 0, false, Color.Black, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                            ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                        }
                        else
                        {
                            ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                            FormatRange(ExcelRange, true, Color.LightGray, true);
                        }
                        Col = IncrementExcelColumn(Col, 1);
                        ExcelRange.EntireColumn.AutoFit();
                    }

                    Col = TopLeftCol;
                    Row++;
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return "";
        }

        public static string InsertActualTargetSummary(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow, System.Data.DataTable dt)
        {
            try
            {
                Range ExcelRange;

                string Col = TopLeftCol;
                int Row = TopLeftRow;

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    Col = TopLeftCol;
                    if (i == 0)
                    {
                        ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), IncrementExcelColumn(Col, 5) + Row.ToString());
                        FormatCell(ExcelRange, "Tổng doanh số của SpiderWeb", 16, true, 15, true, Color.Blue, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                        FormatRange(ExcelRange, true, Color.LightYellow, true);
                    }
                    else
                    {
                        ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), IncrementExcelColumn(Col, 5) + Row.ToString());
                        FormatCell(ExcelRange, "Doanh số Mặt hàng bắt buộc " + i.ToString(), 16, true, 15, true, Color.Blue, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                        FormatRange(ExcelRange, true, Color.LightYellow, true);
                    }

                    Col = IncrementExcelColumn(Col, 6);
                    for (int j = 1; j < dt.Columns.Count; j++)
                    {
                        double value = Convert.ToDouble(dt.Rows[i][j].ToString());
                        if (value == 0)
                        {
                            ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                            FormatCell(ExcelRange, "", 16, true, 15, false, Color.Blue, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                            FormatRange(ExcelRange, false, Color.LightYellow, true);
                        }
                        else
                        {
                            ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                            FormatCell(ExcelRange, value.ToString("#,###.00"), 16, true, 15, false, Color.Blue, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                            FormatRange(ExcelRange, false, Color.LightYellow, true);
                        }
                        Col = IncrementExcelColumn(Col, 1);
                        ExcelRange.EntireColumn.AutoFit();
                    }
                    Row++;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return "";
        }
        #endregion

        #region Create TO Cust Excel file

        public static string InsertTOByCustHeader(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow, System.Data.DataTable dtWeek)
        {
            try
            {
                Range ExcelRange;
                string Col = TopLeftCol;
                int Row = TopLeftRow;


                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "STT", 12, true, 3, true, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Mã\nkhách hàng", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Tên\nkhách hàng", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Loại\nkhách hàng", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Tuyến đường", 12, true, 30, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Chỉ tiêu\nđánh giá", 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "DS bình\nquân/tuần \n(của 4 tuần\n trước", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "DS bình\nquân/tuần \n(của 8 tuần\n trước", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "DS bình\nquân/tuần \n(của 13 tuần\n trước", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), IncrementExcelColumn(Col, 13) + Row.ToString());
                FormatCell(ExcelRange, "Doanh số thực hiện hàng tuần (ngàn đồng)", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                for (int i = 0; i < dtWeek.Rows.Count; i++)
                {
                    ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), Col + (Row + 1).ToString());
                    FormatCell(ExcelRange, "Tuần " + (i + 1).ToString(), 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                    ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                    ExcelRange = ExcelSheet.get_Range(Col + (Row + 2).ToString(), Col + (Row + 2).ToString());
                    FormatCell(ExcelRange, DateTime.Parse(dtWeek.Rows[i][0].ToString()).ToString("MM/dd/yyyy"), 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                    ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                    //go to next column
                    Col = IncrementExcelColumn(Col, 1);
                }

                ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Tổng cộng", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Doanh số thực hiện \n bình quân tuần", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), IncrementExcelColumn(Col, 2) + Row.ToString());
                FormatCell(ExcelRange, "% tăng trưởng", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "So với 4 tuần", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "So với 8 tuần", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "So với 13 tuần", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return "";
        }

        public static string InsertTOByCustDynamicHeader(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow, System.Data.DataTable dt)
        {
            try
            {
                Range ExcelRange;
                string Col = TopLeftCol;
                int Row = TopLeftRow;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), IncrementExcelColumn(Col, 1) + (Row + 1).ToString());
                    FormatCell(ExcelRange, "Chỉ tiêu " + (i + 1).ToString(), 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                    ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                    ExcelRange = ExcelSheet.get_Range(Col + (Row + 2).ToString(), Col + (Row + 2).ToString());
                    FormatCell(ExcelRange, dr["Target"].ToString() + " %", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                    ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                    //go to next column
                    Col = IncrementExcelColumn(Col, 1);

                    ExcelRange = ExcelSheet.get_Range(Col + (Row + 2).ToString(), Col + (Row + 2).ToString());
                    FormatCell(ExcelRange, "% đạt", 12, true, 15, false, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                    ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                    //go to next column
                    Col = IncrementExcelColumn(Col, 1);
                }

                Col = TopLeftCol;
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), IncrementExcelColumn(Col, dt.Rows.Count * 2 - 1) + Row.ToString());
                FormatCell(ExcelRange, "Chỉ tiêu HMP + Thực Phẩm", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, dt.Rows.Count * 2);
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), IncrementExcelColumn(Col, 1) + Row.ToString());
                FormatCell(ExcelRange, "Chỉ tiêu Mặt hàng bắt buộc", 12, true, 40, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Chỉ tiêu", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                Col = IncrementExcelColumn(Col, 1);
                ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "% đạt", 12, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return "";
        }

        public static string InsertTOByCustDetail(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow, System.Data.DataTable dt)
        {
            try
            {
                Range ExcelRange;
                string Col = TopLeftCol;
                int Row = TopLeftRow;
                int k = 1;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];

                    if (i == 0 || !dt.Rows[i][1].ToString().Equals(dt.Rows[i - 1][1].ToString()))
                    {
                        int NoOfRowPerCust = 0;
                        while (i + NoOfRowPerCust < dt.Rows.Count)
                        {
                            if (dt.Rows[i][1].ToString().Equals(dt.Rows[i + NoOfRowPerCust][1].ToString()))
                            {
                                NoOfRowPerCust++;
                            }
                            else
                            {
                                break;
                            }
                        }

                        //STT
                        ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + NoOfRowPerCust - 1).ToString());
                        FormatCell(ExcelRange, k.ToString(), 12, false, 0, true, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                        ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                        k++;
                        Col = IncrementExcelColumn(Col, 1);
                        for (int j = 1; j < 5; j++)
                        {
                            //MaKH, TenKH, LoaiKH, Tuyen duong
                            ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + NoOfRowPerCust - 1).ToString());
                            FormatCell(ExcelRange, CommonClass.ConvertTCVNToUnicode(dr[j].ToString()), 12, false, 0, true, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                            ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                            Col = IncrementExcelColumn(Col, 1);
                            ExcelRange.EntireColumn.AutoFit();
                        }
                    }
                    else
                    {
                        Col = IncrementExcelColumn(Col, 5);
                    }

                    //Chi tieu danh gia
                    ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                    FormatCell(ExcelRange, CommonClass.ConvertTCVNToUnicode(dr[5].ToString().Trim()), 12, false, 0, false, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                    ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                    Col = IncrementExcelColumn(Col, 1);

                    for (int j = 6; j < dt.Columns.Count; j++)
                    {
                        string value = dr[j].ToString();
                        if (!value.Equals(string.Empty))
                        {
                            if (dt.Columns[j].ColumnName.Contains("Percent"))
                            {
                                value = Convert.ToDouble(value).ToString("#,###.00") + " %";
                            }
                            else
                            {
                                value = Convert.ToDouble(value).ToString("#,###.00");
                            }
                            ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                            FormatCell(ExcelRange, value, 12, false, 0, false, Color.Black, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                            ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                        }
                        else
                        {
                            ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                            FormatRange(ExcelRange, true, Color.LightGray, true);
                        }
                        Col = IncrementExcelColumn(Col, 1);
                        ExcelRange.EntireColumn.AutoFit();
                    }

                    Col = TopLeftCol;
                    Row++;
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return "";
        }

        public static string InsertTOByCustSummary(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow, System.Data.DataTable dt)
        {
            try
            {
                Range ExcelRange;

                string Col = TopLeftCol;
                int Row = TopLeftRow;

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    Col = TopLeftCol;

                    ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), IncrementExcelColumn(Col, 5) + Row.ToString());
                    FormatCell(ExcelRange, CommonClass.ConvertTCVNToUnicode(dt.Rows[i][0].ToString().Trim()), 16, true, 15, true, Color.Blue, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                    FormatRange(ExcelRange, true, Color.LightYellow, true);

                    Col = IncrementExcelColumn(Col, 6);
                    for (int j = 1; j < dt.Columns.Count; j++)
                    {
                        string value = dt.Rows[i][j].ToString();
                        if (value.Equals(string.Empty))
                        {
                            ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                            FormatCell(ExcelRange, "", 16, true, 15, false, Color.Blue, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                            FormatRange(ExcelRange, false, Color.LightYellow, true);
                        }
                        else
                        {
                            if (dt.Columns[j].ColumnName.Contains("Percent"))
                            {
                                value = Convert.ToDouble(value).ToString("#,###.00") + " %";
                            }
                            else
                            {
                                value = Convert.ToDouble(value).ToString("#,###.00");
                            }
                            ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + Row.ToString());
                            FormatCell(ExcelRange, value, 16, true, 15, false, Color.Blue, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                            FormatRange(ExcelRange, false, Color.LightYellow, true);
                        }
                        Col = IncrementExcelColumn(Col, 1);
                    }
                    Row++;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return "";
        }
        #endregion

        #region Create TO CAT Excel file

        private static bool IsCurrencyColumn(string colName)
        {
            bool isExist = false;
            string[] lstCurrency = new string[] { "AvgOld4WeekAmt", "AvgOld8WeekAmt", "AvgOld13WeekAmt", "AvgWeekAmt", "Amt1", "Amt2", "Amt3", "Amt4", "Amt5", "Amt6", "Amt7", "Amt8", "Amt9", "Amt10", "Amt11", "Amt12", "Amt13", "TotAmt" };
            for (int i = 0; i < lstCurrency.Length; i++)
                if (lstCurrency[i].Trim().Equals(colName))
                {
                    isExist = true;
                    break;
                }
            return isExist;
        }

        private static bool IsPercentColumn(string colName)
        {
            bool isExist = false;
            string[] lstPercent = new string[] { "Compare4", "Compare8", "Compare13" };
            for (int i = 0; i < lstPercent.Length; i++)
                if (lstPercent[i].Trim().Equals(colName))
                {
                    isExist = true;
                    break;
                }
            return isExist;
        }

        public static void InsertHeaderReport(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow, string distCode, string distName, int cntWA, int cntWB, int fromWeek, int toWeek)
        {
            try
            {
                Range ExcelRange;

                string Col = TopLeftCol;
                int Row = TopLeftRow;

                //Print header and from week, to week of program
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), IncrementExcelColumn(TopLeftCol, 25) + Row.ToString());
                FormatCell(ExcelRange, "Báo cáo doanh số theo ngành hàng", 28, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), IncrementExcelColumn(TopLeftCol, 25) + (Row + 1).ToString());
                FormatCell(ExcelRange, string.Format("Từ tuần: {0} đến tuần : {1}", fromWeek.ToString(), toWeek.ToString()), 28, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);

                //Print distributor information
                ExcelRange = ExcelSheet.get_Range(Col + (Row + 3).ToString(), Col + (Row + 3).ToString());
                FormatCell(ExcelRange, string.Format("Nhà phân phối : {0} [{1}].", distName, distCode), 14, true, 15, false, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);

                //Print Count of Active customer
                ExcelRange = ExcelSheet.get_Range(IncrementExcelColumn(TopLeftCol, 8) + (Row + 3).ToString(), IncrementExcelColumn(TopLeftCol, 8) + (Row + 3).ToString());
                FormatCell(ExcelRange, "Số khách hàng loại WA", 14, true, 15, false, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                ExcelRange = ExcelSheet.get_Range(IncrementExcelColumn(TopLeftCol, 10) + (Row + 3).ToString(), IncrementExcelColumn(TopLeftCol, 10) + (Row + 3).ToString());
                FormatCell(ExcelRange, cntWA.ToString(), 14, true, 15, false, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //Print report date information
                ExcelRange = ExcelSheet.get_Range(Col + (Row + 4).ToString(), Col + (Row + 4).ToString());
                FormatCell(ExcelRange, string.Format("Ngày báo cáo  : {0}.", DateTime.Now.ToShortDateString()), 14, true, 15, false, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);

                //Print Count of Inactive customer
                ExcelRange = ExcelSheet.get_Range(IncrementExcelColumn(TopLeftCol, 8) + (Row + 4).ToString(), IncrementExcelColumn(TopLeftCol, 8) + (Row + 4).ToString());
                FormatCell(ExcelRange, "Số khách hàng loại WB", 14, true, 15, false, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                ExcelRange = ExcelSheet.get_Range(IncrementExcelColumn(TopLeftCol, 10) + (Row + 4).ToString(), IncrementExcelColumn(TopLeftCol, 10) + (Row + 4).ToString());
                FormatCell(ExcelRange, cntWB.ToString(), 14, true, 15, false, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            }
            catch (Exception ex)
            {

            }
        }


        public static void InsertTOCATHeader(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow, DateTime fromDate, DateTime endDate)
        {
            try
            {
                Range ExcelRange;
                string Col = TopLeftCol;
                int Row = TopLeftRow;

                //Create column STT
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "STT", 12, true, 5, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                //Create column 
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Division", 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                //Create column 
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Category", 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                //Create column 
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Brand", 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                //Create column 
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Brandy", 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                //Create column 
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Doanh số bình \n quân/tuần (của \n 04 tuần trước)", 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                //Create column 
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Doanh số bình \n quân/tuần (của \n 08 tuần trước)", 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                //Create column 
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Doanh số bình \n quân/tuần (của \n 13 tuần trước)", 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                //Create columns week1 to week13 
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), IncrementExcelColumn(Col, 13) + (Row).ToString());
                FormatCell(ExcelRange, "Doanh số hằng tuần (1000 VNĐ)", 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                DateTime currentDate = fromDate;
                for (int i = 1; i <= 13; i++)
                {
                    ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), Col + (Row + 1).ToString());
                    FormatCell(ExcelRange, string.Format("Tuần {0}", i.ToString()), 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                    ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                    ExcelRange = ExcelSheet.get_Range(Col + (Row + 2).ToString(), Col + (Row + 2).ToString());
                    FormatCell(ExcelRange, currentDate.ToShortDateString(), 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                    ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                    //go to next column
                    Col = IncrementExcelColumn(Col, 1);
                    currentDate = currentDate.AddDays(7);
                }
                //Create column 
                ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Tổng cộng", 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                //Create column 
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "Doanh số thực \nhiện bình quân", 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);

                //Create column 
                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), IncrementExcelColumn(Col, 2) + Row.ToString());
                FormatCell(ExcelRange, "%Tăng trưởng", 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //Create column 
                ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "So với 04 \ntuần", 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);
                //Create column 
                ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "So với 08 \ntuần", 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);

                //go to next column
                Col = IncrementExcelColumn(Col, 1);
                //Create column 
                ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), Col + (Row + 2).ToString());
                FormatCell(ExcelRange, "So với 13 \ntuần", 12, true, 20, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
            }
            catch (Exception ex)
            {
            }
        }

        public static int InsertTOCATDetail(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow, string programID,string distID)
        {
            DataSet dsTOCAT = TOByCatBO.GetTOCATToExport(programID,distID);

            System.Data.DataTable tblTOCAT = dsTOCAT.Tables[0];
            System.Data.DataTable tblHPC = dsTOCAT.Tables[1];
            System.Data.DataTable tblFoods = dsTOCAT.Tables[2];
            DataRow drSelect;
            Range ExcelRange;
            string Col = TopLeftCol;
            int Row = TopLeftRow;
            int stt = 1;
            double dValue = 0;

            try
            {
                for (int i = 0; i < tblTOCAT.Rows.Count; i++)
                {
                    Col = TopLeftCol;
                    drSelect = tblTOCAT.Rows[i];
                    //STT
                    ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row).ToString());
                    FormatCell(ExcelRange, stt.ToString(), 12, false, 0, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0);
                    ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                    stt++;

                    Col = IncrementExcelColumn(Col, 1);
                    for (int j = 0; j < tblTOCAT.Columns.Count; j++)
                    {
                        ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row).ToString());

                        //Currency Column
                        if (IsCurrencyColumn(tblTOCAT.Columns[j].ColumnName.Trim()))
                        {
                            FormatCell(ExcelRange, string.Format("{0:n3}", Convert.ToDouble(drSelect[tblTOCAT.Columns[j]].ToString())), 12, false, 0, false, Color.Black, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                        }
                        //Percentage column
                        else if (IsPercentColumn(tblTOCAT.Columns[j].ColumnName.Trim()))
                        {
                            dValue = Convert.ToDouble(drSelect[tblTOCAT.Columns[j]].ToString());
                            if (dValue < 1)
                                FormatCell(ExcelRange, string.Format("{0:p2}", dValue), 12, false, 0, false, Color.Black, Color.Red, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                            else
                                FormatCell(ExcelRange, string.Format("{0:p2}", dValue), 12, false, 0, false, Color.Black, Color.LightGreen, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                        }
                        //Text Column
                        else
                        {
                            FormatCell(ExcelRange, drSelect[tblTOCAT.Columns[j]].ToString(), 12, false, 0, true, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                        }

                        ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                        ExcelRange.EntireColumn.AutoFit();
                        Col = IncrementExcelColumn(Col, 1);
                    }
                    Row++;
                }

                //Create summary 
                Col = TopLeftCol;

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), IncrementExcelColumn(Col, 4) + (Row).ToString());
                FormatCell(ExcelRange, "Tổng công HPC", 14, true, 0, true, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                //ExcelRange.EntireColumn.AutoFit();

                ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), IncrementExcelColumn(Col, 4) + (Row + 1).ToString());
                FormatCell(ExcelRange, "Tổng công Foods", 14, true, 0, true, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                //ExcelRange.EntireColumn.AutoFit();

                ExcelRange = ExcelSheet.get_Range(Col + (Row + 2).ToString(), IncrementExcelColumn(Col, 4) + (Row + 2).ToString());
                FormatCell(ExcelRange, "Tổng công HPC+Foods", 14, true, 0, true, Color.Black, XlHAlign.xlHAlignLeft, XlVAlign.xlVAlignCenter, 0);
                ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                //ExcelRange.EntireColumn.AutoFit();

                Col = IncrementExcelColumn(Col, 4);
                for (int j = 0; j < tblHPC.Columns.Count; j++)
                {
                    Col = IncrementExcelColumn(Col, 1);

                    //Currency Column
                    if (IsCurrencyColumn(tblHPC.Columns[j].ColumnName.Trim()))
                    {
                        //Create for HPC row
                        ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row).ToString());
                        FormatCell(ExcelRange, string.Format("{0:n3}", Convert.ToDouble(tblHPC.Rows[0][tblHPC.Columns[j]].ToString())), 12, false, 0, false, Color.Black, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                        ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                        //Create for Foods row
                        ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), Col + (Row + 1).ToString());
                        FormatCell(ExcelRange, string.Format("{0:n3}", Convert.ToDouble(tblFoods.Rows[0][tblFoods.Columns[j]].ToString())), 12, false, 0, false, Color.Black, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                        ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                        //Create for HPC+Foods row
                        ExcelRange = ExcelSheet.get_Range(Col + (Row + 2).ToString(), Col + (Row + 2).ToString());
                        FormatCell(ExcelRange, string.Format("{0:n3}", Convert.ToDouble(tblHPC.Rows[0][tblHPC.Columns[j]].ToString()) + Convert.ToDouble(tblFoods.Rows[0][tblFoods.Columns[j]].ToString())), 12, false, 0, false, Color.Black, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                        ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                    }
                    //Percentage column
                    else if (IsPercentColumn(tblHPC.Columns[j].ColumnName.Trim()))
                    {
                        //HPC row
                        dValue = Convert.ToDouble(tblHPC.Rows[0][tblHPC.Columns[j]].ToString());
                        if (dValue < 1)
                        {
                            ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row).ToString());
                            FormatCell(ExcelRange, string.Format("{0:p2}", dValue), 12, false, 0, false, Color.Black, Color.Red, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                            ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                        }
                        else
                        {
                            ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), Col + (Row).ToString());
                            FormatCell(ExcelRange, string.Format("{0:p2}", dValue), 12, false, 0, false, Color.Black, Color.LightGreen, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                            ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                        }
                        //Foods row
                        dValue = Convert.ToDouble(tblFoods.Rows[0][tblFoods.Columns[j]].ToString());
                        if (dValue < 1)
                        {
                            ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), Col + (Row + 1).ToString());
                            FormatCell(ExcelRange, string.Format("{0:p2}", dValue), 12, false, 0, false, Color.Black, Color.Red, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                            ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                        }
                        else
                        {
                            ExcelRange = ExcelSheet.get_Range(Col + (Row + 1).ToString(), Col + (Row + 1).ToString());
                            FormatCell(ExcelRange, string.Format("{0:p2}", dValue), 12, false, 0, false, Color.Black, Color.LightGreen, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                            ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                        }

                        //HPC + Foods row
                        dValue = Convert.ToDouble(tblHPC.Rows[0][tblHPC.Columns[j]].ToString()) + Convert.ToDouble(tblFoods.Rows[0][tblFoods.Columns[j]].ToString());
                        if (dValue < 1)
                        {
                            ExcelRange = ExcelSheet.get_Range(Col + (Row + 2).ToString(), Col + (Row + 2).ToString());
                            FormatCell(ExcelRange, string.Format("{0:p2}", dValue), 12, false, 0, false, Color.Black, Color.Red, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                            ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                        }
                        else
                        {
                            ExcelRange = ExcelSheet.get_Range(Col + (Row + 2).ToString(), Col + (Row + 2).ToString());
                            FormatCell(ExcelRange, string.Format("{0:p2}", dValue), 12, false, 0, false, Color.Black, Color.LightGreen, XlHAlign.xlHAlignRight, XlVAlign.xlVAlignCenter, 0);
                            ExcelRange.BorderAround(Missing.Value, XlBorderWeight.xlThin, XlColorIndex.xlColorIndexAutomatic, Missing.Value);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return tblTOCAT.Rows.Count + TopLeftRow + 3;
        }

        public static void InsertTOCATFooter(Worksheet ExcelSheet, string TopLeftCol, int TopLeftRow)
        {
            try
            {
                Range ExcelRange;

                string Col = TopLeftCol;
                int Row = TopLeftRow;

                ExcelRange = ExcelSheet.get_Range(Col + Row.ToString(), TopLeftCol + Row.ToString());
                FormatCell(ExcelRange, "Nhà phân phối", 14, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0, true);

                ExcelRange = ExcelSheet.get_Range(IncrementExcelColumn(Col, 6) + Row.ToString(), IncrementExcelColumn(TopLeftCol, 6) + Row.ToString());
                FormatCell(ExcelRange, "Giám sát mãi vụ", 14, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0, true);

                ExcelRange = ExcelSheet.get_Range(IncrementExcelColumn(Col, 12) + Row.ToString(), IncrementExcelColumn(TopLeftCol, 12) + Row.ToString());
                FormatCell(ExcelRange, "Trưởng phòng mãi vụ", 14, true, 15, true, Color.Black, XlHAlign.xlHAlignCenter, XlVAlign.xlVAlignCenter, 0, true);
            }
            catch (Exception ex)
            {
            }
        }
        #endregion
    }
}
